Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning Checkpoints

Checkpoints occur in the Oracle database to ensure that all “dirty blocks” in the SGA are eventually written to disk. Because the DBWR works on a least recently used (LRU) algorithm, data that has been untouched in the SGA for the longest time is written out first. This has the side effect that often-used data blocks may never be written out to disk.

A checkpoint causes all the dirty blocks in the SGA to be written to disk. The following situations can cause a checkpoint:

  Every log switch causes a checkpoint. If a checkpoint is in progress, the log switch overrides it and the checkpoint will restart.
  The initialization parameter LOG_CHECKPOINT_INTERVAL can be set to force a checkpoint when a certain number of redo log blocks have been written relative to the last checkpoint. This is useful when you are running with large log files and want several checkpoints between log switches.
  The initialization parameter LOG_CHECKPOINT_TIMEOUT can be set to cause a checkpoint to occur a specific number of seconds since the beginning of the last checkpoint. This parameter can be used for large redo log files that have a desired checkpoint frequency.
  A checkpoint is forced at the beginning of a tablespace backup only on the affected data files. This checkpoint also overrides any other checkpoint that is in progress.
  A checkpoint is forced on the data files of a tablespace that is brought offline.
  A checkpoint is forced on an instance shutdown. If the administrator shuts down the instance with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE, a checkpoint occurs.
  A checkpoint can be caused manually by the administrator. This checkpoint overrides any in-progress checkpoints.

Depending on how a checkpoint is invoked, a checkpoint can be either a “normal” or “fast” checkpoint. For a normal checkpoint, a few more blocks of data are written by the DBWR when it writes, on behalf of the checkpoint. For a fast checkpoint, a large number of blocks are written by the DBWR when it writes, on behalf of the checkpoint. These blocks are tagged on to the normal activity of the DBWR; while checkpoint blocks are being written, normal DBWR activity is still being done.

In the case of the normal checkpoint, transaction processing is not as affected as it is with the fast checkpoint. With the normal checkpoint, more I/Os are required to complete the checkpoint but the throughput of the system is not drastically affected. Normal checkpoints are invoked by log switches and checkpoint intervals set up with the initialization parameters.

If you notice a drop in performance as a normal checkpoint completes, this can sometimes be compensated for by enabling the CKPT process. Doing so can help if the performance drop is caused by the LGWR process being too busy updating file headers. CKPT updates the file headers, leaving LGWR free to write out the redo entries. To enable the CKPT process, set the initialization parameter CHECKPOINT_PROCESS to TRUE.

With fast checkpoints, transaction processing is affected more drastically. Because a large number of blocks are added to the DBWR’s normal activity, the number of I/Os are reduced but the DBWR is affected by this process. Because the DBWR is busy with the checkpoint, its normal activities are usually affected, causing increased response times and decreased throughput. Fast checkpoints are caused by online tablespace backups, instance shutdowns, and administrator-forced checkpoints.

If you are not as interested in recovery time as you are in throughput, you can set the checkpoint frequency so that a checkpoint is performed only when the redo log switch occurs. If you are interested in quick recovery, set the checkpoint frequency more often. To further increase the checkpoint interval, increase the size of the redo log files.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.